from datetime import datetime, timedelta
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
import requests



# 假设已有数据库连接配置
DATABASE_URL = "mysql+pymysql://chishi_erjiakj_c:raBtRNr21rfw2FjB@1.14.97.95/chishi_erjiakj_c"
engine = create_engine(DATABASE_URL)
Session = sessionmaker(bind=engine)
session = Session()
#订单付费提醒

# 计算时间范围
cutoff_time = datetime.now() - timedelta(seconds=300)
formatted_date = cutoff_time.strftime('%Y-%m-%d %H:%M:%S')

try:
    # 查询 sys_goods_details
    details_query = text("""
        SELECT id, user_id, goods_id, create_date,phone
        FROM sys_goods_details 
        WHERE update_by = 1 
          AND class < 1 
          AND update_date < :date
    """)
    details = session.execute(details_query, {'date': formatted_date}).fetchall()
    for det in details:
        # 检查是否存在符合条件的 sys_sms 记录
        sms_check_query = text("""
            SELECT id 
            FROM sys_sms 
            WHERE type = 1 
              AND details_id = :details_id 
              AND date_time > :date
            LIMIT 1
        """)
        sms_exists = session.execute(sms_check_query, {
            'details_id': det.id,
            'date': formatted_date
        }).fetchone()

        if not sms_exists:
            # 获取商品电话
            goods_query = text("""
                SELECT phone 
                FROM sys_goods 
                WHERE id = :goods_id 
                LIMIT 1
            """)
            goods = session.execute(goods_query, {'goods_id': det.goods_id}).fetchone()
            # print(goods)
            if goods:
                if goods[0]:
                    data = {'phone': goods[0], 'code': 'date', 'value': det.create_date.strftime('%Y-%m-%d'),
                            'sms': 'SMS_474095463'}
                    request = requests.post('https://chishi.erjiakj.cn/index/daily/alysms',data=data)
                    print(request.text)
                    # 发信息给乘客提醒支付
                    data[0] = det[4]
                    data[3] = 'SMS_474315541'
                    requests.post('https://chishi.erjiakj.cn/index/daily/alysms', data=data)
    session.commit()
except Exception as e:
    session.rollback()
    print(f"Error occurred: {str(e)}")
finally:
    session.close()